﻿set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sp_SoDuDauTsList] 
(
	@Ngay_Ct DATETIME = '',
	@Key NVARCHAR(4000) = '',
	@Table_Out VARCHAR(32),
	@Ma_DvCs VARCHAR(3)
)
--WITH ENCRYPTION
AS
BEGIN
	IF Object_Id('Tempdb..#T_SoDuDauTsList') IS NOT NULL DROP TABLE #T_SoDuDauTsList
	IF Object_Id('Tempdb..#T_SoDuDauTsList0') IS NOT NULL DROP TABLE #T_SoDuDauTsList0
	IF Object_Id('Tempdb..#T_SoDuDauTsList1') IS NOT NULL DROP TABLE #T_SoDuDauTsList1

	DECLARE @_SQLExec VARCHAR(4000),
			@_Key NVARCHAR(4000),
			@_KeyCd NVARCHAR(4000),
			@_Nam INT,
			@_Th_Bd_Ht INT, 
			@_Tong_Hop BIT, 
			@_Ngay_Ct1 DATETIME,
			@_Ngay_Ct2 DATETIME

	SET @_Tong_Hop = (SELECT Tong_Hop FROM R00DMDVCS WHERE Ma_DvCs = @Ma_DvCs)
	SET @_Nam = YEAR(@Ngay_Ct) 
	SET @_Th_Bd_Ht = ISNULL((SELECT Th_Bd_Ht FROM R00NAM WHERE Ma_DvCs = @Ma_DvCs AND Nam = @_Nam), 1)

	SELECT	@_Ngay_Ct1 = dbo.fn_GetDate(@_Nam, @_Th_Bd_Ht, 1),
			@_Ngay_Ct2 = @Ngay_Ct - 1

	SELECT	@_Key = '(0 = 0)',
			@_KeyCd = '(0 = 0)'
	
	IF @Key <> ''
		SELECT	@_Key = @_Key + ' AND ' + @Key,
				@_KeyCd = @_KeyCd + ' AND ' + @Key

	IF @_Tong_Hop = 0
		SELECT	@_Key = @_Key + ' AND (Ma_DvCs = ''' + @Ma_DvCs + ''')',
				@_KeyCd = @_KeyCd + ' AND (Ma_DvCs = ''' + @Ma_DvCs + ''')'
	ELSE
		SELECT	@_Key = @_Key + ' AND (Ma_DvCs IN (SELECT Ma_DvCs FROM R00DMDVCS WHERE Tong_Hop = 0))',
				@_KeyCd = @_KeyCd + ' AND (Ma_DvCs IN (SELECT Ma_DvCs FROM R00DMDVCS WHERE Tong_Hop = 0))'
	
	SET @_KeyCd = @_KeyCd + ' AND (Ngay_Ps < ''' + CAST(@Ngay_Ct AS VARCHAR(11)) + ''')'

	SET @_KeyCd = @_KeyCd + ' AND (Stt NOT IN (SELECT Stt FROM R06CtTsNGia WHERE Is_Giam_Ts = 1 AND Ngay_Giam_Ts < ''' + CAST(@Ngay_Ct AS VARCHAR(11)) + '''))'	

	--Lay so du dau nam
	SELECT Ngay_Ps, Ma_Ts, Stt, Ma_Nv, Ma_Tg, Tien_NG, Tien_HM, Tien_CL, Tien_NG_Nt, Tien_HM_Nt, Tien_CL_Nt, Ma_DvCs
		INTO #T_SoDuDauTsList0
		FROM R06CtTsNGia
		WHERE 0 = 1
	
	EXEC Sp_DefaultTable '#T_SoDuDauTsList0'

	--Lay phần nguyên giá
	SET @_SQLExec = '
		SELECT Ngay_Ps, Ma_Ts, Stt, Ma_Nv, Ma_Tg, 
				Tien_NG, Tien_HM , 
				Tien_NG_Nt, Tien_HM_Nt, Ma_DvCs 
			FROM R06CtTsNGia 
			WHERE ' + @_KeyCd

	INSERT INTO #T_SoDuDauTsList0 (Ngay_Ps, Ma_Ts, Stt, Ma_Nv, Ma_Tg, Tien_NG, Tien_HM, Tien_NG_Nt, Tien_HM_Nt, Ma_DvCs)
		EXEC(@_SQLExec)

	--Lấy phần hao mòn
	SET @_SQLExec = '
		SELECT Ngay_Ps, Ma_Ts, Stt, Ma_Nv, 
				Tien_HM, Tien_HM_Nt, Ma_DvCs 
			FROM R06CtTsHMon 
			WHERE ' + @_KeyCd

	INSERT INTO #T_SoDuDauTsList0 (Ngay_Ps, Ma_Ts, Stt, Ma_Nv, Tien_HM, Tien_HM_Nt, Ma_DvCs)
		EXEC(@_SQLExec)

	SELECT  Ma_Ts, Stt, Ma_Nv, Ma_Tg, 
			SUM(Tien_NG) AS So_Du_NG, 
			SUM(Tien_HM) AS So_Du_HM, 
			SUM(Tien_NG - Tien_HM) AS So_Du_CL, 
			SUM(Tien_NG_Nt) AS So_Du_NG_Nt, 
			SUM(Tien_HM_Nt) AS So_Du_HM_Nt, 
			SUM(Tien_NG_Nt - Tien_HM_Nt) AS So_Du_CL_Nt, Ma_DvCs
		INTO #T_SoDuDauTsList
		FROM #T_SoDuDauTsList0
		WHERE ABS(Tien_NG) + ABS(Tien_HM) + ABS(Tien_NG_Nt) + ABS(Tien_HM_Nt) <> 0
		GROUP BY Ma_Ts, Stt, Ma_Nv, Ma_Tg, Ma_DvCs
		ORDER BY Ma_Ts, Stt, Ma_Nv, Ma_Tg, Ma_DvCs	

	EXEC sp_Copy_Data '#T_SoDuDauTsList', @Table_Out, '', ''

	DROP TABLE #T_SoDuDauTsList
	DROP TABLE #T_SoDuDauTsList0

	IF Object_Id('TempDB..#T_SoDuDauTsList1') IS NOT NULL
		DROP TABLE #T_SoDuDauTsList1

END





